package com.sapient;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class SearchServlet
 */
@WebServlet("/SearchServlet")
public class SearchServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public SearchServlet() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String marketid = (String)request.getParameter("marketid");
		request.setAttribute("marketid", marketid);
		request.setAttribute("fromsearch", "true");
		System.out.println("Search : "+marketid);
		RequestDispatcher r = getServletContext().getRequestDispatcher("/MarketEdit.jsp");
		r.forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String location = (request.getParameter("Location")=="")?null:(String)request.getParameter("Location");
		String market = (request.getParameter("Market")=="")?null:(String)request.getParameter("Market");
		String commodity = (request.getParameter("Commodity")=="")?null:(String)request.getParameter("Commodity");
		String currency = (request.getParameter("Currency")=="")?null:(String)request.getParameter("Currency");
		String[][] result = null;
		HttpSession session=request.getSession();
		String user = (String) session.getAttribute("role");
		
		Connection conn = null;
		try
        { 
               Class.forName("oracle.jdbc.driver.OracleDriver");
               conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
               Statement st = conn.createStatement(); 
               int rows = 0;
               String SQL = "";
               String com = "create or replace view comView as select mappingstring from EntityMapping where upper(MappingEntity) like upper('%"+commodity+"%') and entityType = 'Commodity' and status='A'";
               String com2 = "create or replace view comView2 as select mappingentity from comView, EntityMapping e where e.mappingstring = comView.mappingstring";
               String mar = "create or replace view marView as select mappingstring from EntityMapping where upper(MappingEntity) like upper('%"+market+"%') and entityType = 'Market' and status='A'";
               String mar2 = "create or replace view marView2 as select mappingentity from marView, EntityMapping e where e.mappingstring = marView.mappingstring";
               String cur = "create or replace view curView as select mappingstring from EntityMapping where upper(MappingEntity) like upper('%"+currency+"%') and entityType = 'Currency' and status='A'";
               String cur2 = "create or replace view curView2 as select mappingentity from curView, EntityMapping e where e.mappingstring = curView.mappingstring";
               String loc = "create or replace view locView as select mappingstring from EntityMapping where upper(MappingEntity) like upper('%"+location+"%') and entityType = 'Location' and status='A'";
               String loc2 = "create or replace view locView2 as select mappingentity from locView, EntityMapping e where e.mappingstring = locView.mappingstring ";
               if(user.equals("user"))
                     SQL = "select marketid, marketname,commoditytype,currency,location,startdate,enddate from market m where flag='N'";
               else
                     SQL = "select marketid, marketname,commoditytype,currency,location,startdate,enddate,flag from market m where 1=1";
                     
               String c =" and (m.commoditytype in (select distinct commodityType from commodity c,comView v,comView2 v2 where c.commodityType=v.mappingstring or c.commodityType=v2.mappingentity) or upper(m.commoditytype) like upper('%"+commodity+"%'))";
               String m =" and (marketname in (select distinct marketname from market c,marView v,marView2 v2 where c.marketname=v.mappingstring or c.commodityType=v2.mappingentity) or upper(marketname) like upper('%"+market+"%')) ";
               String cu =" and (currency in (select distinct currencyname from currency c,curView v,curView2 v2 where c.currencyname=v.mappingstring or c.currencyname=v2.mappingentity) or upper(currency) like upper('%"+currency+"%')) ";
               String lo =" and (location in (select distinct locationname from location c,locView v,locView2 v2 where c.locationname=v.mappingstring or c.locationname=v2.mappingentity ) or upper(location) like upper('%"+location+"%')) ";
               
               if(commodity!=null)
                     SQL += c;
               if(market!=null)
                     SQL += m;
               if(currency!=null)
                     SQL += cu;
               if(location!=null)
                     SQL += lo;

               st.executeQuery(com);
               st.executeQuery(com2);
               st.executeQuery(mar);
               st.executeQuery(mar2);
               st.executeQuery(cur);
               st.executeQuery(cur2);
               st.executeQuery(loc);
               st.executeQuery(loc2);
               st.executeQuery(SQL);
               ResultSet rs = st.executeQuery(SQL);
               while(rs.next()){
                     rows++;
               }
               
               rs= st.executeQuery(SQL);
               
               result = new String[rows][8];
               int count = 0;
               while(rs.next())
               {
                      System.out.println(rs.getString(1)+rs.getString(2)+rs.getString(3)+rs.getString(4)+rs.getString(5)+rs.getString(6)+rs.getString(7));
                     result[count][0] = rs.getString(1);
                     result[count][1] = rs.getString(2);
                     result[count][2] = rs.getString(3);
                     result[count][3] = rs.getString(4);
                     result[count][4] = rs.getString(5);
                     result[count][5] = rs.getString(6).substring(0,10);
                     String endDate = rs.getString(7);
                     if(endDate!=null)
                            endDate = endDate.substring(0, 10);
                     else
                            endDate = "-";
                     result[count][6] = endDate;
                     result[count][7] = rs.getString(8);
                     count++; 
               }

               conn.close(); 
        }

		catch (Exception e) { 
			System.err.println("Got an exception! "); 
			System.err.println(e.getMessage()); 
			e.printStackTrace();
		} 

		request.setAttribute("location", location);
		request.setAttribute("market", market);
		request.setAttribute("commodity", commodity);
		request.setAttribute("currency", currency);
		request.setAttribute("result", result);
		
		RequestDispatcher r = getServletContext().getRequestDispatcher("/Search.jsp");
		r.forward(request, response);
	}

}
